package itesm.coler.data;

import itesm.coler.models.Course;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class MYSQLCourseDAO implements CourseDAO {

	public MYSQLCourseDAO(){
		
	}
	
	public Course findCourseById(String courseId) throws DAOException {
		 Connection connection = null;
		 PreparedStatement find = null;
	     ResultSet results= null;
	     Course course= null;
	     try{    	
			 connection = MySQLDAOFactory.createConnection();
			 if (connection!=null){
				 find = connection.prepareStatement("select * from course where courseid = ?");
				 find.setString(1,courseId);
				 results = find.executeQuery();
						  if (results.next()) {
							  course = mapResultSetToObject(results);						  			  	
						  }
				}
			}catch(SQLException e){
				throw new DAOException(e);
			} finally {
				DAOUtil.close(connection,find,results);
			}
			return course;
	}

	@Override
	public ArrayList<Course> findAll() throws DAOException, SQLException {

		PreparedStatement findAll = null;
		ResultSet results = null;
		ArrayList<Course> courses = null;
		Connection connection = null;
	    try {
		     connection = MySQLDAOFactory.createConnection();
		     findAll = connection.prepareStatement("select * from course");

			     results = findAll.executeQuery();
			     courses = new ArrayList<Course>();			
			     while(results.next()){
				   Course course = mapResultSetToObject(results);		     
				   courses.add(course);
				}
		 
		   } catch (SQLException e){
			   throw new DAOException(e);
		   } finally {
				DAOUtil.close(connection,findAll,results);
		   }			
		   return courses;
	}
	
	public ArrayList<Course> findAllByProfessor(String professorUserId) throws DAOException {
		Connection connection = null;
		PreparedStatement findAllByProfessor = null;
		ResultSet results = null;
		ArrayList<Course> courses = null;
	    try {
		     connection = MySQLDAOFactory.createConnection();
		     findAllByProfessor = connection.prepareStatement("select * from course where professoruserid=?");
		     synchronized(findAllByProfessor){
		    	 findAllByProfessor.setString(1,professorUserId);
			     results = findAllByProfessor.executeQuery();
			     courses = new ArrayList<Course>();			
			     while(results.next()){
				   Course course = mapResultSetToObject(results);		     
				   courses.add(course);
				}
		     }
		   } catch (SQLException e){
			   throw new DAOException(e);
		   } finally {
				DAOUtil.close(connection,findAllByProfessor,results);
		   }			
		   return courses;
	}
	
	public ArrayList<Course> findAllByStudent(String studentUserId) throws DAOException {
		Connection connection = null;
		PreparedStatement findAllByStudent = null;
		ResultSet results = null;
		ArrayList<Course> courses = null;
	    try {
		     connection = MySQLDAOFactory.createConnection();
		     findAllByStudent = connection.prepareStatement("SELECT course.* FROM course NATURAL JOIN usercourse where userid=?");
		     synchronized(findAllByStudent){
		    	 findAllByStudent.setString(1,studentUserId);
			     results = findAllByStudent.executeQuery();
			     courses = new ArrayList<Course>();			
			     while(results.next()){
				   Course course = mapResultSetToObject(results);		     
				   courses.add(course);
				}
		     }
		   } catch (SQLException e){
			   throw new DAOException(e);
		   } finally {
				DAOUtil.close(connection,findAllByStudent,results);
		   }			
		   return courses;
	}
	
	 // The following methods can use
    // MySQLDAOFactory.createConnection() 
    // to get a connection as required
	 public int insertCourse(Course course) {
		    // Implement insert course here.
		    // Return 0 if OK
		    // or a -1 on error	  		 	
		    Connection connection = null;
		    PreparedStatement insert = null;
		    int result=-1; //error
			 try { 
				    connection = MySQLDAOFactory.createConnection();
				   // connection = ds.getConnection();
					insert = connection.prepareStatement("insert into course(courseid,coursename,active,professoruserid) values(?,?,?,?);");
					insert.setString(1, course.getCourseId());
					insert.setString(2, course.getCourseName());
					insert.setInt(3, 1); //active
					insert.setString(4, course.getProfessorUser().getUserId());
					result = insert.executeUpdate();
					insert.close();
					connection.close();
			} catch (SQLException e) {
				  throw new DAOException(e);
	        } finally {
	              DAOUtil.close(connection, insert);
	        }
			return result;
	 }
		  
	 public boolean updateCourse(Course course) throws DAOException {
		    // implement update record here using data
		    // from the user   Object
		    // Return true on success, false on failure or
		    // error
		    Connection connection = null;
		    PreparedStatement update = null;
		    boolean result = true;
			  try {
				  connection = MySQLDAOFactory.createConnection();
				  update = connection.prepareStatement("UPDATE course SET coursename = ?, professoruserid = ? WHERE courseid = ?");
				  update.setString(1, course.getCourseName());
				  update.setString(2,course.getProfessorUser().getUserId());
				  update.setString(3, course.getCourseId());
				  int affectedRows = update.executeUpdate();
				  if (affectedRows == 0) {
					    result = false;
		                throw new DAOException("Updating course failed, no rows affected.");
		            }
			} catch (SQLException e) {
				 result = false;
				 throw new DAOException(e);
		    } finally {
				DAOUtil.close(connection,update);
			}
			 return result;
		  }
	 public boolean deleteCourse(String username) throws DAOException {
		  // Implement delete user here
		  // Return true on success, false on failure
		  Connection connection = null;
		  PreparedStatement deleteCourse = null;
         boolean result = true;      
		  try {
			    connection = MySQLDAOFactory.createConnection();
			    deleteCourse= connection.prepareStatement("delete from course where courseid = ?");
				deleteCourse.setString(1, username);
				int affectedRows = deleteCourse.executeUpdate();
			    if (affectedRows == 0) {
			    	    result = false;
		                throw new DAOException("Deleting course failed, no rows affected.");
		         }
			  } catch (SQLException e) {
				  result = false;
				  throw new DAOException(e);
			  } finally {
					DAOUtil.close(connection,deleteCourse);
			} 
           return result;
	} 
	 
	 
	  public boolean updateCourseActiveStatus(String courseid, boolean status) throws DAOException {
		  Connection connection = null;
		  PreparedStatement activeStatus = null;
		  boolean result = true;      
				  try {
					    connection = MySQLDAOFactory.createConnection();
					    activeStatus = connection.prepareStatement("UPDATE course SET active = ? WHERE courseid = ?;");
						activeStatus.setBoolean(1, status);
						activeStatus.setString(2, courseid);
						result = activeStatus.execute();
						connection.close();
					  } catch (SQLException e) {
						  result = false;
						  throw new DAOException(e);
					  } finally {
							DAOUtil.close(connection,activeStatus);
					} 
		            return result;
		            
	  }
	  
		
		 public Course mapResultSetToObject(ResultSet results) throws SQLException {
			  Course course = new Course();	
			  course.setCourseId(results.getString("courseid"));
		      course.setActive(results.getBoolean("active"));
		      course.setCourseName(results.getString("coursename"));
		      String professorUserId = results.getString("professoruserid");
		      if (professorUserId != null) {
		         DAOFactory MySQLFactory = DAOFactory.getDAOFactory(DAOFactory.MySQL);
		  	     UserDAO userDAO = MySQLFactory.getUserDAO();
	             course.setProfessorUser(userDAO.findUserByUserId(professorUserId));
	//	      course.setProfessorUserId(professorUserId);
		      }
			  return course;
		 }		 

}
